package com.leexuan.ordersystem.db;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.leexuan.ordersystem.beans.Admin;

public class AdminDB extends DB {

	private String GET_ADMIN = "SELECT * FROM admin WHERE aid=?";
	private String GET_ADMIN_BY_USERNAME = "SELECT * FROM admin WHERE username=?";
	private String ADD_ADMIN = "INSERT INTO admin VALUES (?,?,?)";
	private String GET_ADMINS = "SELECT * FROM admin";
	private String IS_ADMIN = "SELECT * FROM admin WHERE username=? AND password=?";
	private String UPDATE_ADMIN = "UPDATE admin SET name=?,password=? WHERE aid=?";
	private String DEL_ADMIN = "DELETE FROM admin WHERE aid=?";

	public Admin getAdmin(int aid) {
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Admin admin = new Admin();
		try {
			pstmt = conn.prepareStatement(GET_ADMIN);
			pstmt.setInt(1, aid);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				admin.setAid(rs.getInt("aid"));
				admin.setName(rs.getString("name"));
				admin.setUsername(rs.getString("username"));
				admin.setPassword(rs.getString("password"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;
				conn.close();
				conn = null;
			} catch (SQLException ex1) {
				ex1.printStackTrace();
			}
		}
		return admin;
	}

	public Admin getAdminByUsername(String username) {
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Admin admin = new Admin();
		try {
			pstmt = conn.prepareStatement(GET_ADMIN_BY_USERNAME);
			pstmt.setString(1, username);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				admin.setAid(rs.getInt("aid"));
				admin.setName(rs.getString("name"));
				admin.setUsername(rs.getString("username"));
				admin.setPassword(rs.getString("password"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;
				conn.close();
				conn = null;
			} catch (SQLException ex1) {
				ex1.printStackTrace();
			}
		}
		return admin;
	}

	public boolean addAdmin(Admin ad) {
		PreparedStatement pstmt = null;
		try {
			pstmt = conn.prepareStatement(ADD_ADMIN);
			pstmt.setString(1, ad.getName());
			pstmt.setString(2, ad.getUsername());
			pstmt.setString(3, ad.getPassword());

			return pstmt.execute();

		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			try {
				if (pstmt != null) {
					pstmt.close();
					pstmt = null;
				}
			} catch (SQLException ex1) {
				ex1.printStackTrace();
			}
		}
	}

	public boolean updateAdmin(Admin ad, int aid) {
		PreparedStatement pstmt = null;
		try {
			pstmt = conn.prepareStatement(UPDATE_ADMIN);

			pstmt.setString(1, ad.getName());
			pstmt.setString(2, ad.getPassword());
			pstmt.setInt(3, aid);

			return pstmt.execute();

		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			try {
				if (pstmt != null) {
					pstmt.close();
					pstmt = null;
				}
			} catch (SQLException ex1) {
				ex1.printStackTrace();
			}
		}
	}

	public boolean isAdmin(String username, String password) {
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			pstmt = conn.prepareStatement(IS_ADMIN, ResultSet.TYPE_SCROLL_SENSITIVE,
					ResultSet.CONCUR_UPDATABLE);

			pstmt.setString(1, username);
			pstmt.setString(2, password);

			rs = pstmt.executeQuery();
			rs.last();
			if (rs.getRow() != 0) {
				return true;
			} else {
				return false;
			}

		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			try {
				if (pstmt != null) {
					pstmt.close();
					pstmt = null;
				}
			} catch (SQLException ex1) {
				ex1.printStackTrace();
			}
		}
	}

	public ArrayList getAdmins() throws SQLException {

		PreparedStatement pstmt = null;
		ResultSet rs = null;

		ArrayList admins = new ArrayList();

		try {
			pstmt = conn.prepareStatement(GET_ADMINS, ResultSet.TYPE_SCROLL_SENSITIVE,
					ResultSet.CONCUR_UPDATABLE);
			rs = pstmt.executeQuery();

			while (rs.next()) {

				Admin admin = new Admin();
				admin.setAid(rs.getInt("aid"));
				admin.setName(rs.getString("name"));
				admin.setUsername(rs.getString("username"));
				admin.setPassword(rs.getString("password"));
				admins.add(admin);

			}

		} catch (SQLException ex) {
			ex.printStackTrace();

			throw new SQLException("SQLException on : DAO.getAdmins(args)");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;

			} catch (SQLException ex1) {
				ex1.printStackTrace();
			}

		}
		return admins;
	}

	public boolean delAdmin(int aid) {
		PreparedStatement pstmt = null;
		try {
			pstmt = conn.prepareStatement(DEL_ADMIN);

			pstmt.setInt(1, aid);

			return pstmt.execute();

		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			try {
				if (pstmt != null) {
					pstmt.close();
					pstmt = null;
				}
			} catch (SQLException ex1) {
				ex1.printStackTrace();
			}
		}
	}
}
